﻿using MySql.Data.MySqlClient;
using System;
using System.Collections;
using System.Data;
using FCRE.helper;
using System.Data.SqlClient;
using System.IO;
using Newtonsoft.Json;
using FSLib.Network.Http;
using System.Collections.Generic;
using Newtonsoft.Json.Linq;

namespace FCRE
{
    public class JsonContent
    { 
        public string ids { get; set; }
    }
        public class MySqlHelper
    {
        public static string LConn = "";
        //public static string RConn = "";
        public static string Rpwd = "";
        public static string Rport = "";
        public static string Rhost = "";
        public static string mydeptid = "";
        public static string ksid = "";
        public static int qdbid = 0;
        public static int lport = 3309;
        public static string[] tnames = { "exam_paperdef","sys_user", "sys_dept",  "exam_papertpl", "exam_qdb", "exam_qtype", "exam_question","exam_exarrange","exam_scores" };//,"sys_role","sys_user_role"
        public static string[] tnamescn = { "考试任务", "考点用户", "考点", "试卷模板", "题库", "题型", "题目", "考生", "成绩" };//,"sys_role","sys_user_role"
        // 用于缓存参数的HASH表
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

        public static void setlport(int port){
            lport = port;
            LConn = "Database='itfcre';server='127.0.0.1';Port='"+port+"';User Id='root';Password='"+PubVar.DecryptDES(PubVar.lpwd)+"';charset='utf8';pooling=true";
        }
        /// <summary>
        /// 清空所有考试数据
        /// </summary>
        /// <returns></returns>
        public static int cleanAllData()
        {
            try
            {
                MySqlConnection lconn = new MySqlConnection(LConn);
                lconn.Open();
                for (int i = 0; i < tnames.Length; i++)
                {
                    //if (tnames[i].Equals("exam_exarrange")) continue;//不清空考生,监考中有该功能
                    if (tnames[i].Equals("exam_paperdef"))
                    {
                        string commandLine = "delete from " + tnames[i];
                        using (MySqlCommand cmd = new MySqlCommand(commandLine, lconn))
                        {
                            cmd.ExecuteScalar();
                        }
                    }
                    else
                    {
                        string commandLine = "truncate " + tnames[i];//有外键关联不能用此命令
                        using (MySqlCommand cmd = new MySqlCommand(commandLine, lconn))
                        {
                            cmd.ExecuteScalar();
                        }
                    }
                }
                int n = ExecuteNonQuery(LConn, CommandType.Text, "update updn set isend=0");
                return 0;
            }catch(Exception ex)
            {
                LogHelper.WriteLog(ex.Message);
                return 1;
            }
        }
        /// <summary>
        /// 解析远程连接信息
        /// </summary>
        /// <param name="p"></param>
        public static void getDblink(string p)
        {
            string[] zxdbinfo = DESHelper.Decode(p).Split('|');
            Rport = zxdbinfo[1];
            Rpwd = zxdbinfo[3];
            Rhost = zxdbinfo[0];
            //RConn = "Database=" + zxdbinfo[2] + ";server=" + zxdbinfo[0] + ";Port=" + zxdbinfo[1] + ";User Id=root;Password=" + zxdbinfo[3] + ";charset=utf8;pooling=true";
            mydeptid = zxdbinfo[4];
        }
        /// <summary>
        /// 考试ID
        /// </summary>
        /// <returns></returns>
        public static string getPaperid()
        {
            try
            {
                ksid = Convert.ToString(ExecuteScalar(LConn, CommandType.Text, "select id from exam_paperdef where status=1"));
                if (ksid.IsNullOrEmpty()) return "";
                else return ksid;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("Paperdef..." + ex.Message);
                return "";
            }

        }

        /// <summary>
        /// 是否没有完成上传或下载的状态
        /// </summary>
        /// <param name="tname"></param>
        /// <param name="status">0上传、1下载</param>
        /// <returns></returns>
        public static bool getIsNotend(string tname,int status)
        {
            try
            {
                if (Convert.ToByte(ExecuteScalar(LConn, CommandType.Text, "select isend from updn where tname='" + tname + "' and status=" + status)) == 0)
                    return true;
                else return false;
            }catch(Exception ex)
            {
                return true;
            }
        }
        /// <summary>
        /// 总成绩数
        /// </summary>
        /// <returns></returns>
        public static int getN_sc()
        {
            try
            {
                return Convert.ToInt16(ExecuteScalar(LConn, CommandType.Text, "select count(*) from exam_scores"));
            }
            catch (Exception ex)
            {
                return 0;
            }
        }
        /// <summary>
        /// 已上传的成绩数
        /// </summary>
        /// <returns></returns>
        public static int getUP_sc()
        {
            try
            {
                return Convert.ToInt16(ExecuteScalar(LConn, CommandType.Text, "select count(*) from exam_scores where isup=1"));
            }
            catch (Exception ex)
            {
                return 0;
            }
        }
        /// <summary>
        /// 未上传的成绩数
        /// </summary>
        /// <returns></returns>
        public static int getNoUP_sc()
        {
            try
            {
                return Convert.ToInt16(ExecuteScalar(LConn, CommandType.Text, "select count(*) from exam_scores where isup=0"));
            }
            catch (Exception ex)
            {
                return 0;
            }
        }
        /// <summary>
        /// 上传之前清洗可能出现的重复成绩，保留同一人最高分
        /// </summary>
        /// <returns></returns>
        public static int clearScores()
        {
            try//select * FROM exam_scores where ksid="21051410385420408" group by uid having count(uid)>1 
            {//成绩不相同时，删除较小的
                int n = ExecuteNonQuery(LConn, CommandType.Text, "delete from exam_scores where id in(select g.id from(select id from exam_scores where id in (select t.id from (select * from exam_scores where uid in (select uid FROM exam_scores group by uid having count(uid)>1 ))t) and id not in(select id from (select f.uid,max(f.score) score from exam_scores f GROUP BY f.uid having count(f.uid)>1)a left join exam_scores b on a.uid=b.uid and a.score=b.score) )g)");
                LogHelper.WriteLog("清洗成绩数1:"+n);
				//成绩相同时，删除前一条
				n = ExecuteNonQuery(LConn, CommandType.Text, "delete from exam_scores where id in(select g.id from(select id from exam_scores where id in (select t.id from (select * from exam_scores where uid in (select uid FROM exam_scores group by uid having count(uid)>1 ))t) and id not in(select id from (select f.uid,max(f.id) iid from exam_scores f GROUP BY f.uid having count(uid)>1)a left join exam_scores b on a.iid=b.id) )g)");
                LogHelper.WriteLog("清洗成绩数2:"+n);
                return 0;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex.Message);
                return 1;
            }
        }
        /// <summary>
        /// 设置状态为已完成
        /// </summary>
        /// <param name="tname"></param>
        /// <param name="status"></param>
        public static void setIsend(string tname, int status)
        {
            ExecuteNonQuery(LConn, CommandType.Text, "update updn set isend=1 where tname='" + tname + "' and status=" + status);
        }
        /// <summary>
        /// 备份本地数据库
        /// </summary>
        public static int BackUpDB(string backuppath)
        {
            try
            {
                string cpath = PubVar.uctepath + @"\mysql5\bin\";
                string tmpfile = backuppath + "abc.sql";
                string basecmd = cpath + "mysqldump -h 127.0.0.1 -P"+lport+" -uroot -p"+ PubVar.DecryptDES(PubVar.lpwd) + " --opt -c itfcre > " + tmpfile;// 
                CmdHelper cmdHelper = new CmdHelper();
                cmdHelper.cmdExcute(basecmd , 20, 2);
                FileUtils.File2Rar(backuppath+ DateTime.Now.ToString("yyyy-MM-dd-HH-mm") + "db.rar", tmpfile);
                File.Delete(tmpfile);
                
            }
            catch(Exception ex)
            {
                LogHelper.WriteLog("备份数据时..." + ex.Message);
                return 1;
            }
            return 0;
        }
        public static int RestoreDB(string backupfile)
        {
            try
            {
                string cpath = PubVar.uctepath + @"\mysql5\bin\";
                FileInfo fileInfo = new FileInfo(backupfile);
                string backuppath=fileInfo.DirectoryName;
                string tmpfile = backuppath + @"\abc.sql";
                FileUtils.Rar2File(backuppath,backupfile);
                if (!File.Exists(tmpfile)) return 2;
                string basecmd = cpath + "mysql -h 127.0.0.1 -P"+lport+ " -uroot -p" + PubVar.DecryptDES(PubVar.lpwd) + " itfcre < " + tmpfile;// 
                CmdHelper cmdHelper = new CmdHelper();
                string res=cmdHelper.cmdExcute(basecmd, 20, 2);
                if (res.ToLower().Contains("error"))
                {
                    LogHelper.WriteLog("恢复数据时..." + res);
                    return 1;
                }
                
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("恢复数据时..." + ex.Message);
                return 1;
            }
            return 0;
        }
        /// <summary>
        /// 获取表中记录数
        /// </summary>
        /// <param name="tname"></param>
        /// <returns></returns>
        private static int getCount(string tname)
        {
            string commandLine = "SELECT COUNT(*) FROM "+tname;
            MySqlConnection lconn = new MySqlConnection(LConn);
            using (MySqlCommand cmd = new MySqlCommand(commandLine, lconn))
            {
                lconn.Open();
                return Convert.ToInt32(cmd.ExecuteScalar());
            }
        }

        /*

     DataSet dataset = new DataSet();
                try
                {
                    using (MySqlConnection rconn = new MySqlConnection(RConn))
                    {
                        rconn.Open();
                        //================
                        #region 考生
                        MySqlDataAdapter sd = new MySqlDataAdapter();
                        sd.SelectCommand = new MySqlCommand("select id,dept_id,classg,user_name,gender,password,salt from sys_user where dept_id=@dept_id", rconn);
                        sd.SelectCommand.Parameters.AddWithValue("@dept_id", mydeptid);

                        //sd.Fill(dataset, "sys_user");
                        sd.Fill(dataset);
                        sd.Dispose();
                        #endregion
                        //=================
                        rconn.Close();
                    }
                }
                catch (Exception rerr)
                {
                    return "下载中心数据遇到故障：\r\n"+rerr.Message;
                }
                try
                    {
                        using (MySqlConnection lconn = new MySqlConnection(LConn))
                    {
                        lconn.Open();
                        //================
                        #region 考生
                        MySqlDataAdapter sd = new MySqlDataAdapter();
                        sd.InsertCommand = new MySqlCommand("insert into sys_user(id,dept_id,classg,user_name,gender,password,salt)"
                        + " values(@id,@dept_id,@classg,@user_name,@gender,@password,@salt);", lconn);
                        sd.InsertCommand.Parameters.Add("@id", MySqlDbType.VarChar, 64, "id");
                        sd.InsertCommand.Parameters.Add("@dept_id", MySqlDbType.VarChar, 64, "dept_id");
                        sd.InsertCommand.Parameters.Add("@classg", MySqlDbType.VarChar, 64, "classg");
                        sd.InsertCommand.Parameters.Add("@user_name", MySqlDbType.VarChar, 30, "user_name");
                        sd.InsertCommand.Parameters.Add("@gender", MySqlDbType.VarChar, 1, "gender");
                        sd.InsertCommand.Parameters.Add("@password", MySqlDbType.VarChar, 60, "password");
                        sd.InsertCommand.Parameters.Add("@salt", MySqlDbType.VarChar, 60, "salt");

                        sd.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
                        sd.UpdateBatchSize = 0;

                        //建立 CommandBuilder 对象来自动生成 DataAdapter 的 Command 命令，否则就要自己编写
                        //Insertcommand ,deletecommand , updatecommand 命令。
                        MySqlCommandBuilder mySqlCommandBuilder = new MySqlCommandBuilder(sd);
                        // 建立DataTable对象(相当于建立前台的虚拟数据库中的数据表)
                        DataTable datatable = dataset.Tables[0];//dataset.Tables["sys_user"];
                        foreach (DataRow row in datatable.Rows) row.SetAdded();// insert rows only if they are in RowState.Added
                        //int n=sd.Update(dataset,"sys_user");
                        int n = sd.Update(dataset);
                        //dataset.Tables[0].Clear();
                        sd.Dispose();
                        dataset.Dispose();
                        #endregion
                        lconn.Close();
                    }
                }
                catch(Exception lerr)
                {
                    return "加载中心数据遇到故障：\r\n" + lerr.Message;
                } 
         */
        public static bool updateData(string connectionString, DataSet dataset)
        {//每200次更新操作填入一次DataTable，更新10万条用了32秒
            try
            {
                using (MySqlConnection conn = new MySqlConnection(connectionString))//自动释放
                {
                    conn.Open();
                    MySqlDataAdapter sd = new MySqlDataAdapter();
                    sd.UpdateCommand = new MySqlCommand("update exam_scores set isup=@isup where uid=@uid", conn);
                    sd.UpdateCommand.Parameters.Add("@isup", MySqlDbType.Byte, 1, "isup");
                    MySqlParameter prams_ID = sd.UpdateCommand.Parameters.Add("@uid", MySqlDbType.VarChar);
                    prams_ID.SourceColumn = "uid";
                    prams_ID.SourceVersion = DataRowVersion.Original;
                    sd.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
                    sd.UpdateBatchSize = 0;
                    //sd.Fill(dataset);
                    int count = dataset.Tables[0].Rows.Count;
                    for (int i = 0; i < count; i++)
                    {
                        dataset.Tables[0].Rows[i].BeginEdit();
                        dataset.Tables[0].Rows[i]["isup"] = 1;
                        //dataset.Tables[0].Rows[i]["uid"] = "DEVID" + count;
                        dataset.Tables[0].Rows[i].EndEdit();
                    }
                    sd.Update(dataset.Tables[0]);

                    //dataset.Tables[0].Clear();
                    sd.Dispose();
                    //dataset.Dispose();
                    dataset.Clear();
                    conn.Close();
                    return true;
                }
            }catch(Exception ex)
            {
                LogHelper.WriteLog("updatedate..." + ex.Message);
                return false;
            }
        }
        public static bool insertData(string connectionString, DataSet dataset,string tname)
        {//插入10万条数据用时28秒
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                MySqlDataAdapter sd = new MySqlDataAdapter();
                if(tname.Equals("exam_paperdef"))
                sd.SelectCommand = new MySqlCommand("select devid,data_time,data_value from CurrentTest", conn);
                sd.InsertCommand = new MySqlCommand("insert into CurrentTest (devid,data_time,data_value) "
                + " values (@devid,@data_time,@data_value);", conn);
                sd.InsertCommand.Parameters.Add("@devid", MySqlDbType.VarChar, 18, "devid");
                sd.InsertCommand.Parameters.Add("@data_time", MySqlDbType.VarChar, 19, "data_time");
                sd.InsertCommand.Parameters.Add("@data_value", MySqlDbType.Int16, 8, "data_value");
                sd.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
                sd.UpdateBatchSize = 0;

                sd.Fill(dataset);
                Random r = new Random(1000);
                for (int i = 0; i < 100000; i++)
                {
                    object[] row = { "DEVID" + i, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), r.Next(1, 1000) };
                    dataset.Tables[0].Rows.Add(row);
                    if (i % 300 == 0)
                    {
                        sd.Update(dataset.Tables[0]);
                        dataset.Tables[0].Clear();
                    }
                }
                sd.Update(dataset.Tables[0]);
                dataset.Tables[0].Clear();
                sd.Dispose();
                dataset.Dispose();
                conn.Close();
                return true;
            }
        }
        // Insert
        //public bool insertTest(ItemList itemList)
        //{
        //    bool flag = true;
        //    openConn();
        //    string cmdText = "insert into testTable (c1, c2, c3, c4)"
        //                    + "VALUES (@c1, @c2, @c3, @c4)";
        //    MySqlCommand cmd = new MySqlCommand(cmdText, conn);
        //    MySqlTransaction tx = this.dbConn.BeginTransaction();
        //    cmd.Transaction = tx;
        //    try
        //    {
        //        int k = 0;
        //        foreach (Item item in itemList)
        //        {
        //            cmd.Parameters.Clear(); // it is needed
        //            cmd.Parameters.AddWithValue("@c1", item.c1);
        //            cmd.Parameters.AddWithValue("@c2", item.c2);
        //            cmd.Parameters.AddWithValue("@c3", item.c3);
        //            cmd.Parameters.AddWithValue("@c4", item.c4);
        //            cmd.ExecuteNonQuery();
        //            k++;
        //            if (k == 1000) { tx.Commit(); k = 0; }
        //        }
        //        if (k > 0) tx.Commit();//数据量过大时，可以优化为分批提交
        //    }
        //    catch (Exception ex)
        //    {
        //        flag = false;
        //        if(tx!=null)tx.Rollback();
        //    }
        //    finally
        //    {
        //        cmd.Dispose();
        //        if(tx!=null)tx.Dispose();
        //    }
        //    closeConn();
        //    return flag;
        //}
        /// <summary>
        /// 以事务形式上传下载，确保数据完整
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static int ExecuteTrans(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {
            MySqlTransaction trans = null;
            MySqlConnection conn = null;
            try
            {
                MySqlCommand cmd = new MySqlCommand();
                conn = new MySqlConnection(connectionString);
                conn.Open();
                trans = conn.BeginTransaction();
                PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
                int val = cmd.ExecuteNonQuery();
                trans.Commit();
                
                cmd.Parameters.Clear();
                return val;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("trans..."+ex.Message);
                if (trans != null) trans.Rollback();
                return -1;
            }
            finally
            {
                if (trans != null) trans.Dispose();
                conn.Close();
            }
        }

        /// <summary>
        ///  给定连接的数据库用假设参数执行一个sql命令（不返回数据集）
        /// </summary>
        /// <param name="connectionString">一个有效的连接字符串</param>
        /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
        /// <param name="cmdText">存储过程名称或者sql命令语句</param>
        /// <param name="commandParameters">执行命令所用参数的集合</param>
        /// <returns>执行命令所影响的行数</returns>
        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {

            MySqlCommand cmd = new MySqlCommand();

            using (MySqlConnection conn = new MySqlConnection(connectionString))//自动释放
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// 用现有的数据库连接执行一个sql命令（不返回数据集）
        /// </summary>
        /// <param name="connection">一个现有的数据库连接</param>
        /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
        /// <param name="cmdText">存储过程名称或者sql命令语句</param>
        /// <param name="commandParameters">执行命令所用参数的集合</param>
        /// <returns>执行命令所影响的行数</returns>
        public static int ExecuteNonQuery(MySqlConnection connection, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {

            MySqlCommand cmd = new MySqlCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        ///使用现有的SQL事务执行一个sql命令（不返回数据集）
        /// </summary>
        /// <remarks>
        ///举例:
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="trans">一个现有的事务</param>
        /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
        /// <param name="cmdText">存储过程名称或者sql命令语句</param>
        /// <param name="commandParameters">执行命令所用参数的集合</param>
        /// <returns>执行命令所影响的行数</returns>
        public static int ExecuteNonQuery(MySqlTransaction trans, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {
            MySqlCommand cmd = new MySqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// 用执行的数据库连接执行一个返回数据集的sql命令
        /// </summary>
        /// <remarks>
        /// 举例:
        ///  MySqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">一个有效的连接字符串</param>
        /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
        /// <param name="cmdText">存储过程名称或者sql命令语句</param>
        /// <param name="commandParameters">执行命令所用参数的集合</param>
        /// <returns>包含结果的读取器</returns>
        public static MySqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {
            //创建一个MySqlCommand对象
            MySqlCommand cmd = new MySqlCommand();
            //创建一个MySqlConnection对象
            MySqlConnection conn = new MySqlConnection(connectionString);

            //在这里我们用一个try/catch结构执行sql文本命令/存储过程，因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在，
            //因此commandBehaviour.CloseConnection 就不会执行
            try
            {
                //调用 PrepareCommand 方法，对 MySqlCommand 对象设置参数
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                //调用 MySqlCommand  的 ExecuteReader 方法
                //CommandBehavior.CloseConnection的功能保证当SqlDataReader对象被关闭时，其依赖的连接也会被自动关闭。
                MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                //清除参数
                cmd.Parameters.Clear();
                return reader;
            }
            catch
            {
                //关闭连接，抛出异常
                conn.Close();
                throw;
            }
        }

        /// <summary>
        /// 返回DataSet
        /// </summary>
        /// <param name="connectionString">一个有效的连接字符串</param>
        /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
        /// <param name="cmdText">存储过程名称或者sql命令语句</param>
        /// <param name="commandParameters">执行命令所用参数的集合</param>
        /// <returns></returns>
        public static DataSet GetDataSet(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {
            //创建一个MySqlCommand对象
            MySqlCommand cmd = new MySqlCommand();
            //创建一个MySqlConnection对象
            MySqlConnection conn = new MySqlConnection(connectionString);
            //在这里我们用一个try/catch结构执行sql文本命令/存储过程，因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在，
            try
            {
                //调用 PrepareCommand 方法，对 MySqlCommand 对象设置参数
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                //调用 MySqlCommand  的 ExecuteReader 方法
                MySqlDataAdapter adapter = new MySqlDataAdapter();
                adapter.SelectCommand = cmd;
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                //清除参数
                cmd.Parameters.Clear();
                conn.Close();
                return ds;
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        /// <summary>
        /// 用指定的数据库连接字符串执行一个命令并返回一个数据集的第一列
        /// </summary>
        /// <remarks>
        ///例如:
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
        /// </remarks>
        ///<param name="connectionString">一个有效的连接字符串</param>
        /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
        /// <param name="cmdText">存储过程名称或者sql命令语句</param>
        /// <param name="commandParameters">执行命令所用参数的集合</param>
        /// <returns>用 Convert.To{Type}把类型转换为想要的 </returns>
        public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {
            MySqlCommand cmd = new MySqlCommand();

            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }
        /// <summary>
        /// 用指定的数据库连接执行一个命令并返回一个数据集的第一列
        /// </summary>
        /// <remarks>
        /// 例如:
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">一个存在的数据库连接</param>
        /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
        /// <param name="cmdText">存储过程名称或者sql命令语句</param>
        /// <param name="commandParameters">执行命令所用参数的集合</param>
        /// <returns>用 Convert.To{Type}把类型转换为想要的 </returns>
        public static object ExecuteScalar(MySqlConnection connection, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {

            MySqlCommand cmd = new MySqlCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }
        /// <summary>
        /// 将参数集合添加到缓存
        /// </summary>
        /// <param name="cacheKey">添加到缓存的变量</param>
        /// <param name="commandParameters">一个将要添加到缓存的sql参数集合</param>
        public static void CacheParameters(string cacheKey, params MySqlParameter[] commandParameters)
        {
            parmCache[cacheKey] = commandParameters;
        }
        /// <summary>
        /// 找回缓存参数集合
        /// </summary>
        /// <param name="cacheKey">用于找回参数的关键字</param>
        /// <returns>缓存的参数集合</returns>
        public static MySqlParameter[] GetCachedParameters(string cacheKey)
        {
            MySqlParameter[] cachedParms = (MySqlParameter[])parmCache[cacheKey];

            if (cachedParms == null)
                return null;

            MySqlParameter[] clonedParms = new MySqlParameter[cachedParms.Length];

            for (int i = 0, j = cachedParms.Length; i < j; i++)
                clonedParms[i] = (MySqlParameter)((ICloneable)cachedParms[i]).Clone();

            return clonedParms;
        }
        /// <summary>
        /// 准备执行一个命令
        /// </summary>
        /// <param name="cmd">sql命令</param>
        /// <param name="conn">OleDb连接</param>
        /// <param name="trans">OleDb事务</param>
        /// <param name="cmdType">命令类型例如 存储过程或者文本</param>
        /// <param name="cmdText">命令文本,例如:Select * from Products</param>
        /// <param name="cmdParms">执行命令的参数</param>
        private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms)
        {

            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (MySqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }


    }
}
